﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
//导入命名空间
using Aspose.Cells;
using System.Collections;
using System.Drawing;
using stwh_Common;
using System.IO;

namespace stwh_Common
{
    /// <summary>
    /// Aspose操作类
    /// </summary>
    public class AsposeHelper
    {
        /// <summary> 
        /// 导出Excel数据到本地 
        /// </summary> 
        /// <param name="dt">要导出的数据</param> 
        /// <param name="tableName">表格标题</param> 
        /// <param name="sheetname">sheet名称</param>
        /// <param name="path">保存路径</param> 
        /// <param name="nameList">列标题集合</param>
        public static void OutFileToDisk(DataTable dt, string tableName, string sheetname, string path, Dictionary<string, string> nameList)
        {
            try
            {
                string fontfamily = "微软雅黑";
                Workbook workbook = new Workbook(); //工作簿 
                Worksheet sheet = workbook.Worksheets[0]; //工作表 
                if (!string.IsNullOrEmpty(sheetname)) sheet.Name = sheetname;
                //sheet.Protect(ProtectionType.All, "123123", "");//保护工作表
                sheet.Protection.AllowSelectingLockedCell = false;//设置只能选择解锁单元格
                sheet.Protection.AllowFormattingCell = true;//设置可以调整列
                sheet.Protection.AllowFormattingRow = true;//设置可以调整行
                Cells cells = sheet.Cells;//单元格 

                #region 样式设置
                //大标题样式
                Style styleTitle = workbook.CreateStyle();//新增样式 
                styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 
                styleTitle.Font.Name = fontfamily;//文字字体 
                styleTitle.Font.Size = 18;//文字大小 
                styleTitle.Font.IsBold = true;//粗体
                styleTitle.Font.Color = Color.White;//字体颜色
                //设置前景色（必须设置Pattern属性值，否则前景色无法显示）
                styleTitle.ForegroundColor = Color.FromArgb(51, 122, 183);
                //设置图案样式为实体填充
                styleTitle.Pattern = BackgroundType.Solid;

                //数据列标题样式
                Style style2 = workbook.CreateStyle();//新增样式 
                style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中 
                style2.Font.Name = fontfamily;//文字字体 
                style2.Font.Size = 14;//文字大小 
                style2.Font.IsBold = true;//粗体 
                style2.IsTextWrapped = true;//单元格内容自动换行
                style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

                //数据列样式
                Style style3 = workbook.CreateStyle();//新增样式 
                style3.HorizontalAlignment = TextAlignmentType.Center;//文字居中 
                style3.Font.Name = fontfamily;//文字字体 
                style3.Font.Size = 12;//文字大小 
                style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

                #endregion

                int Colnum = nameList.Count;//表格列数 
                int Rownum = dt.Rows.Count;//表格行数 

                //设置大标题样式、内容、行高
                cells.Merge(0, 0, 1, Colnum);//合并单元格 
                cells[0, 0].PutValue(tableName);//填写内容 
                cells[0, 0].SetStyle(styleTitle);
                cells.SetRowHeight(0, 38);//设置第一行高度,参数0表示第一行索引

                int colIndex = 0;
                //生成列标题行
                foreach (KeyValuePair<string, string> item in nameList)
                {
                    string value = item.Value;
                    string[] ss = new string[1];
                    ss[0] = "{split}";
                    string[] valuesplit = value.Split(ss, StringSplitOptions.RemoveEmptyEntries);
                    if (valuesplit.Length > 1)
                    {
                        cells[1, colIndex].PutValue(valuesplit[0]);
                        if (PageValidate.IsNumber(valuesplit[1])) cells.SetColumnWidth(colIndex, double.Parse(valuesplit[1]));
                    }
                    else
                    {
                        cells[1, colIndex].PutValue(valuesplit[0]);
                        cells.SetColumnWidth(colIndex, 20d);
                    }
                    cells[1, colIndex].SetStyle(style2);
                    colIndex++;
                }
                cells.SetRowHeight(1, 25);

                //生成数据行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    colIndex = 0;
                    foreach (KeyValuePair<string, string> item in nameList)
                    {
                        cells[2 + i, colIndex].PutValue(dt.Rows[i][item.Key].ToString());
                        cells[2 + i, colIndex].SetStyle(style3);
                        colIndex++;
                    }
                    cells.SetRowHeight(2 + i, 24);
                }

                workbook.Save(path);
            }
            catch (Exception ex)
            {
                FileHelper.WriteError("导出excel数据出错，错误描述：" + ex.Message);
            }
        }

        /// <summary>
        /// 导入Excel数据
        /// </summary>
        /// <param name="path">excel文件路径（绝对路径）</param>
        /// <returns></returns>
        public static DataTable InFileToData(string path)
        {
            try
            {
                using (FileStream fstream = new FileStream(path, FileMode.Open))
                {
                    Workbook workbook = new Workbook(fstream);
                    Cells cells = workbook.Worksheets[0].Cells;
                    //MaxDataRow、MaxDataColumn表示的是最大索引，而不是数据中的总行数、总列数
                    return cells.ExportDataTable(1, 0, cells.MaxRow, cells.MaxColumn + 1);
                }
            }
            catch (Exception ex)
            {
                FileHelper.WriteError("导入excel数据出错，错误描述：" + ex.Message);
                return null;
            }
        }
    }
}
